package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/lib/pq"
)

const (
	host     = "192.168.0.23"
	port     = 5437
	user     = "hub"
	password = "1234qwer"
	dbname   = "go-test"
)

type User struct {
	ID   int
	Name string
	Age  int
}

type Member struct {
	ID     int
	Name   string
	Age    int
	Title  string
	Status *string
}

func main() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		log.Fatalf("Unable to connect to the database: %v", err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		log.Fatalf("Unable to ping the database: %v", err)
	}

	fmt.Println("Successfully connected to the database!")

	// Example usage

	// Example usage for creating and updating table schema
	exampleTableManagement(db)

	if err := createUser(db, "Alice", 30); err != nil {
		fmt.Println("Error creating user:", err)
	}

	users, err := readUsers(db)
	if err != nil {
		fmt.Println("Error reading users:", err)
	} else {
		fmt.Println("Users:", users)
	}

	// Example usage for Member table
	if err := createMember(db, "Bob", 40, "Manager"); err != nil {
		fmt.Println("Error creating member:", err)
	}

	members, err := readMembers(db)
	if err != nil {
		fmt.Println("Error reading members:", err)
	} else {
		fmt.Println("Members:", members)
	}

	if err := updateMember(db, 1, "Bob Updated", 41, "Senior Manager"); err != nil {
		fmt.Println("Error updating member:", err)
	}

	if err := deleteMember(db, 1); err != nil {
		fmt.Println("Error deleting member:", err)
	}

}

func createUser(db *sql.DB, name string, age int) error {
	query := "INSERT INTO users (name, age) VALUES ($1, $2)"
	_, err := db.Exec(query, name, age)
	return err
}

func readUsers(db *sql.DB) ([]User, error) {
	query := "SELECT id, name, age FROM users"
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		if err := rows.Scan(&user.ID, &user.Name, &user.Age); err != nil {
			return nil, err
		}
		users = append(users, user)
	}
	return users, nil
}

func updateUser(db *sql.DB, id int, name string, age int) error {
	query := "UPDATE users SET name = $1, age = $2 WHERE id = $3"
	_, err := db.Exec(query, name, age, id)
	return err
}

func deleteUser(db *sql.DB, id int) error {
	query := "DELETE FROM users WHERE id = $1"
	_, err := db.Exec(query, id)
	return err
}

func createMember(db *sql.DB, name string, age int, title string) error {
	query := "INSERT INTO member (name, age, title) VALUES ($1, $2, $3)"
	_, err := db.Exec(query, name, age, title)
	return err
}

func readMembers(db *sql.DB) ([]Member, error) {
	query := "SELECT id, name, age, title, status FROM member"
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var members []Member
	for rows.Next() {
		var member Member
		if err := rows.Scan(&member.ID, &member.Name, &member.Age, &member.Title, &member.Status); err != nil {
			return nil, err
		}
		members = append(members, member)
	}
	return members, nil
}

func updateMember(db *sql.DB, id int, name string, age int, title string) error {
	query := "UPDATE member SET name = $1, age = $2, title = $3 WHERE id = $4"
	_, err := db.Exec(query, name, age, title, id)
	return err
}

func deleteMember(db *sql.DB, id int) error {
	query := "DELETE FROM member WHERE id = $1"
	_, err := db.Exec(query, id)
	return err
}

func createTable(db *sql.DB, tableName string, schema string) error {
	query := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (%s)", tableName, schema)
	_, err := db.Exec(query)
	return err
}

func updateTableSchema(db *sql.DB, tableName string, columnName string, columnType string) error {
	query := fmt.Sprintf("ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s %s", tableName, columnName, columnType)
	_, err := db.Exec(query)
	return err
}

// Example usage for creating and updating table schema
func exampleTableManagement(db *sql.DB) {
	// Create users table
	userSchema := "id SERIAL PRIMARY KEY, name VARCHAR(100), age INT"
	if err := createTable(db, "users", userSchema); err != nil {
		fmt.Println("Error creating users table:", err)
	}

	// Update users table schema
	if err := updateTableSchema(db, "users", "email", "VARCHAR(255)"); err != nil {
		fmt.Println("Error updating users table schema:", err)
	}

	// Create member table
	memberSchema := "id SERIAL PRIMARY KEY, name VARCHAR(100), age INT, title VARCHAR(100)"
	if err := createTable(db, "member", memberSchema); err != nil {
		fmt.Println("Error creating member table:", err)
	}

	// Update member table schema
	if err := updateTableSchema(db, "member", "status", "VARCHAR(50)"); err != nil {
		fmt.Println("Error updating member table schema:", err)
	}
}
